# 从mysql中读取数据，转换为DataFrame，然后将DataFrame转换为字典，最后将字典序列化写入redis内存
# max11s有五百多，min8.5s有两百多
import pandas as pd
import pymysql
import ast
import redis
import umsgpack
from settings import REDIS_CON, MAX_VIDEO_TIME, VIDEO_MySQL_PARAMS
# MAX_VIDEO_TIME = 8.5

column_video_name = [
    "id",
    "md5",
    "video_time",
    "embeddings_datas",
    "local_path"
]

column_image_name = [
    "id",
    "md5",
    "embeddings_datas",
    "local_path"
]


redis_conn = redis.Redis(**REDIS_CON)

def get_init_image_data(column_name):
    mysql_conn = pymysql.connect(**VIDEO_MySQL_PARAMS)
    mysql_course = mysql_conn.cursor()
    select_image_embed_sql = "SELECT id, image_md5, embeddings_labels, local_path FROM image_info WHERE embeddings_labels IS NOT NULL AND download_status = 1  AND width / height BETWEEN 1.38 AND 1.7"
    mysql_course.execute(select_image_embed_sql)
    result = mysql_course.fetchall()
    # 将结果列表中的元组转换为字典
    result_dict = [dict(zip(column_name, row)) for row in result]
    # 将字典转换为 DataFrame
    image_df = pd.DataFrame(result_dict)
    return image_df

def get_init_video_data(column_name):
    mysql_conn = pymysql.connect(**VIDEO_MySQL_PARAMS)
    mysql_course = mysql_conn.cursor()
    select_video_embed_sql = "SELECT id, video_md5, video_time, embeddings_labels_description, local_path FROM video_info WHERE video_time<%s  AND download_status = 1 AND video_width =1280 AND video_height=720" % (MAX_VIDEO_TIME)
    mysql_course.execute(select_video_embed_sql)
    result = mysql_course.fetchall()
    # 将结果列表中的元组转换为字典
    result_dict = [dict(zip(column_name, row)) for row in result]
    # 将字典转换为 DataFrame
    video_df = pd.DataFrame(result_dict)
    return video_df


def transform_data(df):
    # import ast 导入ast的literal_eval模块用于解析字符串为列表
    # 将嵌入向量obj转换为浮点类型
    df['embeddings_datas'] = df['embeddings_datas'].apply(
        lambda x: [float(value) for value in ast.literal_eval(x)])
    return df


def serialize_data(df, column_name, redis_key):
    # dataframe转化为hash键值对格式，获取存量数据已经序列化保存到redis内存的数据
    # 精简数据，然后进行序列化，写入内存
    new_df = df.loc[:, column_name]
    # 将df转化为字典然后写入hash mapping进行储存
    data_dict = new_df.to_dict(orient='list')

    serialized_data = umsgpack.packb(data_dict)
    redis_conn.hset(redis_key, 'umsgpack_video_data', serialized_data)


def clean_data(redis_key):
    redis_conn.delete(redis_key)
    print('清除完成')


def image_run():
    image_df = get_init_image_data(column_image_name)
    image_df = transform_data(image_df)
    redis_key = 'total_image_embed_tag'
    serialize_data(image_df, column_image_name, redis_key)
    print('转换完成')

def video_run():
    video_df = get_init_video_data(column_video_name)
    video_df = transform_data(video_df)
    redis_key = 'total_video_embed_tag'
    serialize_data(video_df, column_video_name, redis_key)
    print('转换完成')


# image_run()
video_run()
# redis_key = 'total_video_embed_tag'
# clean_data(redis_key)
